package com.kongwg.blob;

import com.kongwg.util.JDBCUtil;
import com.kongwg.util.JDBCUtil1;
import org.junit.Test;

import java.sql.Connection;
import java.sql.PreparedStatement;

/**
 * @author kongwg
 * @create 2021-01-08 15:37
 */
public class InsertTest {

    /**
     * 数据批量导入
     */
    @Test
    public void testInsert() throws Exception {
        Connection conn = JDBCUtil1.getconnection();
        String sql = "insert into goods(name) values (?)";
        PreparedStatement ps = conn.prepareStatement(sql);
        for (int i = 0; i < 1000000; i++) {
            ps.setString(1, "name_" + i);
            ps.execute();
        }

        JDBCUtil1.closeResource(conn, ps);
    }


    /**
     * 使用batch缓存
     * * 批量插入的方式三：
     * * 1.addBatch()、executeBatch()、clearBatch()
     * * 2.mysql服务器默认是关闭批处理的，我们需要通过一个参数，让mysql开启批处理的支持。
     * * 		 ?rewriteBatchedStatements=true 写在配置文件的url后面
     * * 3.使用更新的mysql 驱动：mysql-connector-java-5.1.37-bin.jar
     *
     * @throws Exception
     */
    @Test
    public void testInsert1() throws Exception {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil1.getconnection();
            String sql = "insert into goods(name) values(?)";
            //关闭事务
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sql);

            long start = System.currentTimeMillis();
            for (int i = 0; i < 1000000; i++) {
                ps.setString(1, "name_" + i);
                ps.addBatch();
                if (i % 500 == 0) {
                    ps.executeBatch();
                }
                ps.clearBatch();

            }
            conn.commit();
            long end = System.currentTimeMillis();
            System.out.println(end - start);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            JDBCUtil.closeResource(conn, ps);
        }
    }
}
